﻿using EazyCMS.Common;
using EazyCMS.DBUtility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace EazyCMS.Web.Plugin.Naire.DAL
{
    public partial class naire
    {
        private string databaseprefix; //数据库表名前缀
        public naire(string _databaseprefix)
        {
            databaseprefix = _databaseprefix;
        }

        #region 基本方法=============================
        /// <summary>
		/// 是否存在该记录
		/// </summary>
		public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from " + databaseprefix + "naire");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Model.naire model)
        {
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        StringBuilder strSql = new StringBuilder();
                        strSql.Append("insert into " + databaseprefix + "naire(");
                        strSql.Append("title,content,sort_id,start_time,end_time,add_time)");
                        strSql.Append(" values (");
                        strSql.Append("@title,@content,@sort_id,@start_time,@end_time,@add_time)");
                        strSql.Append(";select @@IDENTITY");
                        SqlParameter[] parameters = {
                                new SqlParameter("@title", SqlDbType.NVarChar,255),
                                new SqlParameter("@content", SqlDbType.NVarChar),
                                new SqlParameter("@sort_id", SqlDbType.Int,4),
                                new SqlParameter("@start_time", SqlDbType.DateTime),
                                new SqlParameter("@end_time", SqlDbType.DateTime),
                                new SqlParameter("@add_time", SqlDbType.DateTime) };


                        parameters[0].Value = model.title;
                        parameters[1].Value = model.content;
                        parameters[2].Value = model.sort_id;
                        parameters[3].Value = model.start_time;
                        parameters[4].Value = model.end_time;
                        parameters[5].Value = model.add_time;

                        //添加主表数据
                        object obj = DbHelperSQL.GetSingle(conn, trans, strSql.ToString(), parameters); //带事务
                        model.id = Convert.ToInt32(obj);

                        //投票选项列表
                        //if (model.options != null)
                        //{
                        //    StringBuilder strSql2;
                        //    foreach (Model.vote_option modelt in model.options)
                        //    {
                        //        strSql2 = new StringBuilder();
                        //        strSql2.Append("insert into " + databaseprefix + "naire_option(");
                        //        strSql2.Append("vote_id,title,vote_num,sort_id)");
                        //        strSql2.Append(" values (");
                        //        strSql2.Append("@vote_id,@title,@vote_num,@sort_id)");
                        //        SqlParameter[] parameters2 = {
                        //                new SqlParameter("@vote_id", SqlDbType.Int,4),
                        //                new SqlParameter("@title", SqlDbType.NVarChar,255),
                        //                new SqlParameter("@vote_num", SqlDbType.Int,4),
                        //                new SqlParameter("@sort_id", SqlDbType.Int,4)};
                        //        parameters2[0].Value = model.id;
                        //        parameters2[1].Value = modelt.title;
                        //        parameters2[2].Value = modelt.vote_num;
                        //        parameters2[3].Value = modelt.sort_id;
                        //        DbHelperSQL.GetSingle(conn, trans, strSql2.ToString(), parameters2);
                        //    }
                        //}
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        return -1;
                    }
                }
            }
            return model.id;
        }

        /// <summary>
        /// 修改一列数据
        /// </summary>
        public void UpdateField(int id, string strValue)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + databaseprefix + "naire set " + strValue);
            strSql.Append(" where id=" + id);
            DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Model.naire model)
        {
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        #region 主表信息============================
                        StringBuilder strSql = new StringBuilder();
                        strSql.Append("update " + databaseprefix + "naire set ");

                        strSql.Append("title=@title,");
                        strSql.Append("content=@content,");

                        strSql.Append("sort_id=@sort_id,");
                        strSql.Append("start_time=@start_time,");
                        strSql.Append("end_time=@end_time");

                        strSql.Append(" where id=@id");
                        SqlParameter[] parameters = {

                                new SqlParameter("@title", SqlDbType.NVarChar,255),
                                new SqlParameter("@content", SqlDbType.NVarChar),

                                new SqlParameter("@sort_id", SqlDbType.Int,4),
                                new SqlParameter("@start_time", SqlDbType.DateTime),
                                new SqlParameter("@end_time", SqlDbType.DateTime),

                                new SqlParameter("@id", SqlDbType.Int,4)};

                        parameters[0].Value = model.title;
                        parameters[1].Value = model.content;

                        parameters[2].Value = model.sort_id;
                        parameters[3].Value = model.start_time;
                        parameters[4].Value = model.end_time;
                        //


                        parameters[5].Value = model.id;
                        DbHelperSQL.ExecuteSql(conn, trans, strSql.ToString(), parameters);
                        #endregion

                        #region 修改投票选项========================
                        //删除已删除的投票选项
                        //DeleteList(conn, trans, model.options, model.id);
                        ////添加/修改规格值
                        //if (model.options != null)
                        //{
                        //    StringBuilder strSql2;
                        //    foreach (Model.vote_option modelt in model.options)
                        //    {
                        //        strSql2 = new StringBuilder();
                        //        if (modelt.id > 0)
                        //        {
                        //            strSql2.Append("update " + databaseprefix + "naire_option set ");
                        //            strSql2.Append("title=@title,");
                        //            strSql2.Append("vote_num=@vote_num,");
                        //            strSql2.Append("sort_id=@sort_id");
                        //            strSql2.Append(" where id=@id");
                        //            SqlParameter[] parameters2 = {
                        //                    new SqlParameter("@title", SqlDbType.NVarChar,255),
                        //                    new SqlParameter("@vote_num", SqlDbType.Int,4),
                        //                    new SqlParameter("@sort_id", SqlDbType.Int,4),
                        //                    new SqlParameter("@id", SqlDbType.Int,4)};
                        //            parameters2[0].Value = modelt.title;
                        //            parameters2[1].Value = modelt.vote_num;
                        //            parameters2[2].Value = modelt.sort_id;
                        //            parameters2[3].Value = modelt.id;
                        //            DbHelperSQL.ExecuteSql(conn, trans, strSql2.ToString(), parameters2);
                        //        }
                        //        else
                        //        {
                        //            strSql2 = new StringBuilder();
                        //            strSql2.Append("insert into " + databaseprefix + "naire_option(");
                        //            strSql2.Append("vote_id,title,vote_num,sort_id)");
                        //            strSql2.Append(" values (");
                        //            strSql2.Append("@vote_id,@title,@vote_num,@sort_id)");
                        //            SqlParameter[] parameters2 = {
                        //                new SqlParameter("@vote_id", SqlDbType.Int,4),
                        //                new SqlParameter("@title", SqlDbType.NVarChar,255),
                        //                new SqlParameter("@vote_num", SqlDbType.Int,4),
                        //                new SqlParameter("@sort_id", SqlDbType.Int,4)};
                        //            parameters2[0].Value = model.id;
                        //            parameters2[1].Value = modelt.title;
                        //            parameters2[2].Value = modelt.vote_num;
                        //            parameters2[3].Value = modelt.sort_id;
                        //            DbHelperSQL.ExecuteSql(conn, trans, strSql2.ToString(), parameters2);
                        //        }
                        //    }
                        //}
                        #endregion

                        #region 更新投票总数量======================
                        //StringBuilder strSql3 = new StringBuilder();
                        //strSql3.Append("update " + databaseprefix + "naire_item set ");
                        //strSql3.Append(" vote_count=(select sum(vote_num) from " + databaseprefix + "naire_option where vote_id=@vote_id)");
                        //strSql3.Append(" where id=@vote_id");
                        //SqlParameter[] parameters3 = {
                        //                    new SqlParameter("@vote_id", SqlDbType.Int,4)};
                        //parameters3[0].Value = model.id;
                        //DbHelperSQL.ExecuteSql(conn, trans, strSql3.ToString(), parameters3);
                        #endregion

                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        return false;
                    }
                }
            }
            return true;
        }

        /// <summary>
        /// 删除一条数据，及子表所有相关数据
        /// </summary>


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.naire GetModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 id,title,content,sort_id,start_time,end_time,add_time");
            strSql.Append(" from " + databaseprefix + "naire ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);

            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,title,content,sort_id,start_time,end_time,add_time ");
            strSql.Append(" FROM " + databaseprefix + "naire");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by sort_id asc,add_time desc");
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" id,title,content,sort_id,start_time,end_time,add_time");
            strSql.Append(" FROM " + databaseprefix + "naire");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得查询分页数据
        /// </summary>
        public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM " + databaseprefix + "naire");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
        }
        #endregion

        #region 扩展方法=============================
        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.naire DataRowToModel(DataRow row)
        {
            Model.naire model = new Model.naire();
            if (row != null)
            {
                #region 主表信息======================
                if (row["id"] != null && row["id"].ToString() != "")
                {
                    model.id = int.Parse(row["id"].ToString());
                }

                if (row["title"] != null && row["title"].ToString() != "")
                {
                    model.title = row["title"].ToString();
                }
                if (row["content"] != null && row["content"].ToString() != "")
                {
                    model.content = row["content"].ToString();
                }


                if (row["sort_id"] != null && row["sort_id"].ToString() != "")
                {
                    model.sort_id = int.Parse(row["sort_id"].ToString());
                }
                if (row["start_time"] != null && row["start_time"].ToString() != "")
                {
                    model.start_time = DateTime.Parse(row["start_time"].ToString());
                }
                if (row["end_time"] != null && row["end_time"].ToString() != "")
                {
                    model.end_time = DateTime.Parse(row["end_time"].ToString());
                }
                if (row["add_time"] != null && row["add_time"].ToString() != "")
                {
                    model.add_time = DateTime.Parse(row["add_time"].ToString());
                }


                #endregion


            }
            return model;
        }

        /// <summary>
        /// 是否已过期
        /// </summary>
        public bool IsExpired(int naire_id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from " + databaseprefix + "naire");
            strSql.Append(" where datediff(d,start_time,getdate())>=0 and datediff(d,end_time,getdate())<=0 and id=@naire_id");
            SqlParameter[] parameters = {
                    new SqlParameter("@naire_id", SqlDbType.Int,4)};
            parameters[0].Value = naire_id;

            bool result = DbHelperSQL.Exists(strSql.ToString(), parameters);
            if (result)
            {
                return false;
            }
            return true;
        }

        #endregion

    }
}
    

